package com.tbsoft.ehr.dao.contract;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.tbsoft.ehr.dao.BaseDaoImpl;
import com.tbsoft.ehr.model.Contract;
import com.tbsoft.ehr.model.StaffInfo;

public class ContractDaoImpl extends BaseDaoImpl<Contract> implements ContractDao{

	private static final Log log = LogFactory.getLog(ContractDaoImpl.class);
	@Override
	public Contract getContractById(String id) {
		return (Contract)getHibernateTemplate().get(Contract.class, id);
	}

	@Override
	public List<Contract> getContractByStaffId(String staffId) {
		final String id = staffId;
		
		return getHibernateTemplate().executeFind(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q= session.createQuery("from Contract c where c.staff = ? and c.status <>'D' order by c.endTime desc");
				q.setString(0, id);
				return q.list();
			}
		});
	}

	//获取合同序号
	/*@Override
	public int getContractNo(String staffId) {
		final String id = staffId;
		Integer res = (Integer)getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {				
				Query q = session.createSQLQuery("SELECT MAX(CONTRACT_NO) FROM EHR_CONTRACT GROUP BY STAFF_ID HAVING STAFF_ID=?");
				q.setString(0, id);
				return q.uniqueResult();
			}
		});	
		if(res!=null){
			return res;
		}
		return 0;
	}*/

	@SuppressWarnings("unchecked")
	@Override
	public List<Contract> getContractByCondton(List<StaffInfo> staffLst,
			String endTimeFrom, String endTimeTo,final int startIndex,final int count) {
		//如果用户没有输入任何条件，返回所有用户的最近合同
		if(staffLst==null&&endTimeFrom==null&&endTimeTo==null) {
			StringBuilder sb=new StringBuilder();
			sb.append("from Contract e1 ");
			sb.append("where e1.endTime =(select max(e2.endTime) from Contract e2 where e2.staff=e1.staff) and e1.status<>'D' ");
			sb.append("order by e1.endTime desc");
			
			final String sbStr = sb.toString();
			List<Contract> contractLst = this.getHibernateTemplate().executeFind(new HibernateCallback() {			
				@Override
				public Object doInHibernate(Session session) throws HibernateException,
						SQLException {
					return session.createQuery(sbStr).setFirstResult(startIndex).setMaxResults(count).list();
				}
			});						
			return contractLst;
		} 
		
		//根据条件查询
		StringBuilder sb2 = new StringBuilder("from Contract c where c.status<>'D' ");
		if(endTimeFrom!=null&&endTimeTo!=null) {
			sb2.append("and c.endTime between '").append(endTimeFrom).append("' and '").append(endTimeTo).append("' ");
		}
		StringBuilder sb3 =new StringBuilder();
		if(staffLst!=null) {	
			sb3.append("and c.staff in(");
			for(StaffInfo si:staffLst) {
				sb3.append("'").append(si.getStaffId()).append("',");
			}
			int i = sb3.lastIndexOf(",");
			sb3.replace(i, i+1, ") ");			
		} 
		sb3.append(" order by c.endTime desc");
		final String queryStr = sb2.toString()+sb3.toString();
		List<Contract> contractLst = getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				return session.createQuery(queryStr).setFirstResult(startIndex).setMaxResults(count).list();
			}
		});
		return contractLst;		
	}
	
	@Override
	public int getContractCount(List<StaffInfo> staffLst,String endTimeFrom, String endTimeTo) {
		if(staffLst==null&&endTimeFrom==null&&endTimeTo==null) {
			StringBuilder sb=new StringBuilder();
			sb.append("select count(*) from Contract e1 ");
			sb.append("where e1.endTime =(select max(e2.endTime) from Contract e2 where e2.staff=e1.staff) and e1.status<>'D' ");
			
			final String sbStr = sb.toString();
			Long count = (Long)getHibernateTemplate().execute(new HibernateCallback() {			
				@Override
				public Object doInHibernate(Session session) throws HibernateException,
						SQLException {
					return session.createQuery(sbStr).uniqueResult();
				}
			});
			return count.intValue();
		}
		
		StringBuilder sb2 = new StringBuilder("select count(*) from Contract c where c.status<>'D' ");
		if(endTimeFrom!=null&&endTimeTo!=null) {
			sb2.append("and c.endTime between '").append(endTimeFrom).append("' and '").append(endTimeTo).append("' ");
		}
		
		StringBuilder sb3 = new StringBuilder();
		if(staffLst!=null) {	
			sb3.append("and c.staff in(");			
			for(StaffInfo si:staffLst) {
				sb3.append("'").append(si.getStaffId()).append("',");
			}
			sb3.append("'')");			
		} 
		final String queryStr = sb2.toString()+sb3.toString();
		Long count = (Long)getHibernateTemplate().execute(new HibernateCallback() {			
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				return session.createQuery(queryStr).uniqueResult();
			}
		});
		return count.intValue();
	}

	@Override
	public void logicDelete(final String staffId) {
		getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("update Contract c set c.status='D' where c.staff=?");
				q.setString(0, staffId);
				return q.executeUpdate();
			}
		});
	}

	

}
